package guoanmaker.personal.business.dao;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import guoanmaker.personal.business.model.OperatorCoupons;
import guoanmaker.personal.business.model.ProvideOrderForm;
import guoanmaker.personal.business.modelnobind.Coupons;
import guoanmaker.personal.business.modelnobind.ProvideOrderFormForOrder;
import guoanmaker.personal.helper.common.util.SystemHelper;

/**
 *
 * @ClassName: PersonalOrderFormDao 
 * @Description: 订单dao
 * @author Yvonne_MGL
 * @date 2017年7月12日 下午2:03:11    
 */
@Repository
public class PersonalOrderFormDao {
	@PersistenceContext
	EntityManager em;

	/**
	 *
	 * @Description: 根据用户id和订单状态查询订单 
	 * @param orderStatus
	 *            订单状态
	 * @param userid
	 *            用户id
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年7月12日 下午4:36:30    
	 * @return List<ProvideOrderFormForOrder>    @throws 
	 */
	public List<ProvideOrderFormForOrder> findByProperties(String orderStatus, String userid) {
		String jpql = "select new guoanmaker.personal.business.modelnobind.ProvideOrderFormForOrder"
				+ "(o.id,o.orderNumber,o.placeOrderTime,o.quantity,o.unitPrice,o.truePrice,o.orderStatus,o.remark,o.defrayTime,"
				+ "o.acceptOrderTime,o.defrayNumber,o.applyforCompleteTime,o.confirmTime,o.applyforRefundsTime,"
				+ "o.confirmRefundsTime,o.denyRefundsTime,o.denyRefundsReason,o.contactPerson,o.contactNumber,"
				+ "o.requirementDescription,o.isOverChangePrice,o.orderType,o.userCompany,o.visitTime,o.visitorsNum,"
				+ "o.rentedBeginDate,o.rentedEndDate,o.rentedNum,o.commission,o.bail,o.fee,u.username,u.nickname,u.email,"
				+ "u.remark,c.commodityName,t.serverName,o.closeTime,c.invoiceMail,o.invoiceState,c.commodity_smallimage,"
				+ "b.businessName,b.id,b.companyName,c.id,o.closeType,o.serverPlace,o.preferentialMoney,b.LicensPhone,"
				+ "o.couponName,o.refundsReason,o.spikeid,o.spikePrice,o.enterpriseName,o.position,o.returnPreferentialMoney) from ProvideOrderForm o left join o.user u left join o.commodity c "
				+ "left join c.providerBusiness b left join c.serviceType t where 1=1 ";
		if (orderStatus != null && !orderStatus.equals("")) {
			if (orderStatus.equals("5")) {
				jpql += " and (o.orderStatus='5' or o.orderStatus='9' or o.orderStatus='10' or o.orderStatus='11') ";
			} else if(orderStatus.equals("7")){
				jpql += " and (o.orderStatus='7' or o.orderStatus='8' or o.orderStatus='12' or o.orderStatus='15') ";
			}
			else {
				jpql += " and o.orderStatus='" + orderStatus + "' " ;
			}
		}
		if (userid != null && !userid.equals("")) {
			jpql += "and u.id='" + userid + "'";
		}
		jpql += " order by o.placeOrderTime desc ";
		Query query = em.createQuery(jpql);

		List<ProvideOrderFormForOrder> result = query.getResultList();
		List<ProvideOrderFormForOrder> list=new ArrayList<ProvideOrderFormForOrder>();
		for (int i = 0; i < result.size(); i++) {
			ProvideOrderFormForOrder order=result.get(i);
			order.setLicensPhone("4008518585");
			list.add(order);
		}
		return list;
	}

	/**
	 *
	 * @Description: 根据id获取订单详情 
	 * @param id
	 *            订单id
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年7月12日 下午4:37:52    
	 * @return ProvideOrderFormForOrder    @throws 
	 */
	public ProvideOrderFormForOrder findByid(String id) {
		ProvideOrderFormForOrder order = new ProvideOrderFormForOrder();
		String jpql = "select new guoanmaker.personal.business.modelnobind.ProvideOrderFormForOrder"
				+ "(o.id,o.orderNumber,o.placeOrderTime,o.quantity,o.unitPrice,o.truePrice,o.orderStatus,o.remark,o.defrayTime,"
				+ "o.acceptOrderTime,o.defrayNumber,o.applyforCompleteTime,o.confirmTime,o.applyforRefundsTime,"
				+ "o.confirmRefundsTime,o.denyRefundsTime,o.denyRefundsReason,o.contactPerson,o.contactNumber,"
				+ "o.requirementDescription,o.isOverChangePrice,o.orderType,o.userCompany,o.visitTime,o.visitorsNum,"
				+ "o.rentedBeginDate,o.rentedEndDate,o.rentedNum,o.commission,o.bail,o.fee,u.username,u.nickname,u.email,"
				+ "u.remark,c.commodityName,t.serverName,o.closeTime,c.invoiceMail,o.invoiceState,c.commodity_smallimage,"
				+ "b.businessName,b.id,b.companyName,c.id,o.closeType,o.serverPlace,o.preferentialMoney,b.LicensPhone,o.couponName,"
				+ "o.refundsReason,o.spikeid,o.spikePrice,o.enterpriseName,o.position,o.returnPreferentialMoney) from ProvideOrderForm o left join o.user u left join o.commodity c left join c.providerBusiness b "
				+ "left join c.serviceType t where o.id='" + id + "'";
		Query query = em.createQuery(jpql);
		List<ProvideOrderFormForOrder> result = query.getResultList();
		if (result != null && result.size() > 0) {
			order = result.get(0);
			order.setLicensPhone("4008518585");
		}
		return order;
	}

	/**
	 *
	 * @Description: 根据服务商id查询服务商一共交过的保证金金额
	 * @param businessid 服务商id
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年8月9日 下午3:24:55    
	 * @return int   
	 * @throws 
	 */
	public int getSumBail(String businessid) {
		String jpql = "select sum(o.bail) from ProvideOrderForm o join o.commodity c join c.providerBusiness b where b.id='"
				+ businessid + "'";
		Query query = em.createQuery(jpql);
		List list = query.getResultList();
		BigDecimal sumBail = (BigDecimal) list.get(0);
		if (sumBail == null) {
			sumBail = new BigDecimal(0.00);
		}
		int result = Integer.parseInt(
				(sumBail.doubleValue() * 100 + "").substring(0, (sumBail.doubleValue() * 100 + "").indexOf(".")));
		return result;
	}

	/**
	 *
	 * @Description: 查询大于今天凌晨开始生成的订单 
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年8月17日 上午10:10:40    
	 * @return int   
	 * @throws 
	 */
	public String getOrderNumber(){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String timeBegin=format.format(date)+" 00:00:00";
		String jpql="select o from ProvideOrderForm o where o.placeOrderTime>='"+timeBegin+"'";
		Query query = em.createQuery(jpql);
		List<ProvideOrderForm> result = query.getResultList();
		int sum=0;
		if(result!=null){
			sum=result.size();
		}
		String str="G"+format.format(date)+(sum+1);
		str=str.replace("-", "");
		return str;
	}

	/**
	 *
	 * @Description: 查询app端已发起支付的订单
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年8月21日 下午3:12:19    
	 * @return List<ProvideOrderForm>   
	 * @throws 
	 */
	public List<ProvideOrderForm> getOrders(){
		String time=SystemHelper.getLess15Min();
		String jpql="select o from ProvideOrderForm o where ( o.orderStatus='1'  or (o.orderStatus='7' and o.closeTime>'"+time+"')) and o.defrayNumber<>'' and o.defrayNumber is not null  ";
		Query query = em.createQuery(jpql);
		List<ProvideOrderForm> result = query.getResultList();
		return result;
	}

	/**
	 *
	 * @Description: 查询用户发生退款的订单 
	 * @param userid 用户id
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年8月24日 下午3:55:10    
	 * @return List<ProvideOrderFormForOrder>   
	 * @throws 
	 */
	public List<ProvideOrderFormForOrder> findRefundsOrders(String userid){
		String jpql = "select new guoanmaker.personal.business.modelnobind.ProvideOrderFormForOrder"
				+ "(o.id,o.orderNumber,o.placeOrderTime,o.quantity,o.unitPrice,o.truePrice,o.orderStatus,o.remark,o.defrayTime,"
				+ "o.acceptOrderTime,o.defrayNumber,o.applyforCompleteTime,o.confirmTime,o.applyforRefundsTime,"
				+ "o.confirmRefundsTime,o.denyRefundsTime,o.denyRefundsReason,o.contactPerson,o.contactNumber,"
				+ "o.requirementDescription,o.isOverChangePrice,o.orderType,o.userCompany,o.visitTime,o.visitorsNum,"
				+ "o.rentedBeginDate,o.rentedEndDate,o.rentedNum,o.commission,o.bail,o.fee,u.username,u.nickname,u.email,"
				+ "u.remark,c.commodityName,t.serverName,o.closeTime,c.invoiceMail,o.invoiceState,c.commodity_smallimage,"
				+ "b.businessName,b.id,b.companyName,c.id,o.closeType,o.serverPlace,o.preferentialMoney,b.LicensPhone,o.couponName,"
				+ "o.refundsReason,o.spikeid,o.spikePrice,o.enterpriseName,o.position,o.returnPreferentialMoney) from ProvideOrderForm o left join o.user u left join o.commodity c "
				+ "left join c.providerBusiness b left join c.serviceType t where 1=1 and u.id='"+userid+"' ";

		jpql += " and (o.orderStatus='5' or o.orderStatus='8' or o.orderStatus='9' or o.orderStatus='10' or o.orderStatus='11' or o.orderStatus='12') ";
		jpql += " order by o.placeOrderTime desc ";
		Query query = em.createQuery(jpql);
		List<ProvideOrderFormForOrder> result = query.getResultList();
		List<ProvideOrderFormForOrder> list=new ArrayList<ProvideOrderFormForOrder>();
		for (int i = 0; i < result.size(); i++) {
			ProvideOrderFormForOrder order=result.get(i);
			order.setLicensPhone("4008518585");
			list.add(order);
		}
		return list;
	}

	/**
	 *
	 * @Description: 查询可放优惠券 
	 * @param vouchtype 查询类型 1为注册送   2为抽奖送  3为累积签到送  4为连续签到送 5为推荐送
	 * @return
	 * @author Yvonne_MGL
	 * @date 2017年8月28日 下午3:07:33    
	 * @return List<OperatorCoupons>   
	 * @throws 
	 */
	public List<OperatorCoupons> findOperatorCoupons(String vouchtype){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String jpql="select c from OperatorCoupons c where  "
				+ " c.startcouponstime<=now() and c.endcouponstime>=now() and c.iseffect=1 "
				+ "and c.delestatus!=1 and (c.datetype='0' or (c.datetype='1' and c.endofdate>='"+time+"'))";
		if(vouchtype==null||vouchtype.equals("")){
			jpql+=" and 1=0 ";
		}else{
			jpql+=" and c.vouchtype='"+vouchtype+"' ";
		}
		System.out.println(jpql);
		Query query = em.createQuery(jpql);
		List<OperatorCoupons> result = query.getResultList();
		return result;
	}

	/**
	 *
	 * @Description: 查询用户有效的优惠券 
	 * @param userid 用户id
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年8月28日 下午3:57:53    
	 * @return List<Coupons>   
	 * @throws 
	 */
	public List<Coupons> findCoupons(String userid){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0' and puc.expiredDate>='"+time+"' and u.id='"+userid+"'   order by c.fullamount";
		Query query = em.createQuery(jpql);
		List<Coupons> result = query.getResultList();
		return result;
	}

	/**
	 *
	 * @Description: 分页查询用户有效的优惠券 
	 * @param userid 用户id
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年8月28日 下午3:57:53    
	 * @return List<Coupons>   
	 * @throws 
	 */
	public Map findCouponsByPage(String userid, Integer pageNumber, Integer pageSize ,Integer orderBy){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0' and puc.expiredDate>='"+time+"' and u.id='"+userid+"'";
		String jpqlOrderBy = null;
		if (orderBy==null || orderBy==1) {
			jpqlOrderBy=jpql+" order by puc.startDate desc";
		}
		if (orderBy!=null && orderBy==2) {
			jpqlOrderBy=jpql+" order by puc.expiredDate";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}

	/* *
	 * @Description:分页查询用户已经使用的优惠券
	 * @author JTing
	 * @date 2017/11/22 14:22
	 * @param [userid, pageNumber, pageSize ,orderBy]
	 * @return java.util.Map
	 */
	public Map findUsedCouponsByPage(String userid, Integer pageNumber, Integer pageSize ,Integer orderBy) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='1'  and u.id='"+userid+"'";
		String jpqlOrderBy = null;
		if (orderBy == null || orderBy == 1) {
			jpqlOrderBy= jpql + " order by puc.startDate desc";
		}
		if (orderBy!=null && orderBy == 2) {
			jpqlOrderBy= jpql + " order by puc.expiredDate";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}
	/* *
	 * @Description:分页查询用户未使用已经过期的优惠卷(半年内)
	 * @author JTing
	 * @date 2017/11/22 14:28
	 * @param [userid, pageNumber, pageSize,orderBy]
	 * @return java.util.Map
	 */
	public Map findExpiredCouponsByPage(String userid, Integer pageNumber, Integer pageSize ,Integer orderBy) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		Date date2 = new Date(System.currentTimeMillis()-90L*24*60*60*1000);
		String time=format.format(date);
		String time2 = format.format(date2);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0' and puc.expiredDate<'"+time+"' and u.id='"+userid+"' and  puc.expiredDate>'"+time2+"' ";
		String jpqlOrderBy = null;
		if (orderBy==null || orderBy==1) {
			jpqlOrderBy = jpql + " order by puc.expiredDate";
		}
		if (orderBy!=null && orderBy == 2) {
			jpqlOrderBy=jpql+ " order by puc.startDate desc";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}

	/* *
	 * @Description: 根据serverid分页查询可用的优惠卷
	 * @author JTing
	 * @date 2017/11/22 16:35
	 * @param [sid,uid,pageNumber,pageSize,orderBy]  serverid
	 * @return java.util.Map
	 */
	public Map findUsefulCouponsByPage(String sid, String uid, Double realPrice, Integer pageNumber, Integer pageSize ,Integer orderBy) {
		List<String> lCouponsLimitList = em.createNativeQuery("select valuess from t_key_value where keyss='lapse_coupons_amount_limit'").getResultList();
		double lCouponsLimit = Double.parseDouble(lCouponsLimitList.get(0) + "");
		List<String> ratioList = em.createNativeQuery("select valuess from t_key_value where keyss='direct_reduction_ratio'").getResultList();
		double ratio = Double.parseDouble(ratioList.get(0) + "") / 100;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0'  and puc.expiredDate>='"+time+"' and c.serverid in ('0','"+sid+"') and u.id='"+uid+"' and ((c.couponstype='1'  and c.fullamount<="+realPrice +")   or (c.couponstype!='1' and c.lapseamount<="+realPrice*ratio+" and "+realPrice+">="+lCouponsLimit+"))";
		String jpqlOrderBy = null;
		if (orderBy == null || orderBy == 1) {
			jpqlOrderBy = jpql+ " order by c.couponstype ,puc.startDate desc";
		}
		if (orderBy!=null && orderBy == 2) {
			jpqlOrderBy =jpql + " order by c.couponstype ,puc.expiredDate";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
/*		Iterator<Coupons> iterator = list.iterator();
		while (iterator.hasNext()) {
			if (realPrice >= iterator.next().getFullamount()) {
				//do nothing
			} else {
				iterator.remove();
			}
		}*/
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}

	/* *
 * @Description: 根据serverid分页查询不可用的优惠卷
 * @author JTing
 * @date 2017/11/22 16:35
 * @param [sid,uid,pageNumber,pageSize,orderBy]  serverid
 * @return java.util.Map
 */
	public Map findNoUsefulCouponsByPage(String sid,String uid , Double realPrice,Integer pageNumber, Integer pageSize ,Integer orderBy) {
		List<String> lCouponsLimitList = em.createNativeQuery("select valuess from t_key_value where keyss='lapse_coupons_amount_limit'").getResultList();
		double lCouponsLimit = Double.parseDouble(lCouponsLimitList.get(0) + "");
		List<String> ratioList = em.createNativeQuery("select valuess from t_key_value where keyss='direct_reduction_ratio'").getResultList();
		double ratio = Double.parseDouble(ratioList.get(0) + "") / 100;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0'  and puc.expiredDate>='"+time+"' and u.id='"+uid+"' and (c.serverid not in ('0','"+sid+"') or (c.couponstype='1' and c.fullamount>"+realPrice+") or (c.couponstype!='1'  and (c.lapseamount>"+realPrice*ratio +" or "+realPrice+" <"+lCouponsLimit+" )))";
		String jpqlOrderBy = null;
		if (orderBy == null || orderBy == 1) {
			jpqlOrderBy = jpql+ " order by c.couponstype ,puc.startDate desc";
		}
		if (orderBy!=null && orderBy == 2) {
			jpqlOrderBy =jpql + " order by c.couponstype ,puc.expiredDate";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}
	/**
	 *
	 * @Description: 查询用户已过期和已使用的优惠券
	 * @param userid 用户id
	 * @param pageNumber 分页页数，从0开始
	 * @param pageSize 每页条数
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2017年11月14日 上午9:17:54    
	 * @return Map   
	 * @throws 
	 */
	public  Map findAllCouponsByPage(String userid, Integer pageNumber, Integer pageSize){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where ( puc.isUsed='1' or puc.expiredDate<'"+time+"') and u.id='"+userid+"' order by puc.expiredDate desc";
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpql);
		List<Coupons> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;

	}
	/* *
	 * @Description:查询用户可用,已用,过期的优惠卷的数目
	 * @author JTing
	 * @date 2017/11/27 9:04
	 * @param [userid]
	 * @return java.util.Map
	 */
	public Map findAllTypeCouponsCountbyuserid(String userid) {
		//jpa原生sql查询多个字段样例
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		Date dateExp = new Date(System.currentTimeMillis()-90L*24*60*60*1000);
		String time=format.format(date);
		String timeExp = format.format(dateExp);
		String jpql="select " +
				"sum(case when is_used='0' and  expired_date>=date'"+time+"'  and user_id='"+userid+"' then 1 else 0 end) useableNum, " +
				"sum(case when is_used='1'  and user_id='"+userid+"' then 1 else 0 end) usedNum, " +
				"sum(case when is_used='0' and  expired_date<date'"+time+"'  and user_id='"+userid+"' and expired_date>date'"+timeExp+"' then 1 else 0 end) expiredNum " +
				"from t_personal_user_coupon;";
		Query nativeQuery = em.createNativeQuery(jpql);
		List rows = nativeQuery.getResultList();
		String useableNum=null;
		String usedNum=null;
		String expiredNum=null;
		for (Object row: rows
				) {
			Object[] cells = (Object[]) row;
			useableNum=cells[0]+"";
			usedNum=cells[1]+"";
			expiredNum=cells[2] +"";
		}

		Map map=new HashMap();
		map.put("useableNum", useableNum==null?"0":useableNum);
		map.put("usedNum", usedNum==null?"0":usedNum);
		map.put("expiredNum", expiredNum==null?"0":expiredNum);
		return map;
	}
	/* *
     * @Description:获得用户领取的双12优惠卷id
     * @author JTing
     * @date 2017/12/01 9:00
     * @param [userid]
     * @return java.util.Map
     */
	public Map getUser1212Coupons(String userid) {
		//jpa原生sql查询单一字段样例
//		String sql="select  oc_id   from t_personal_user_coupon where user_id='"+userid+"' and oc_id in ('8aaf0aab6011fc1f016015d02aa40078','8aaf0aab6011fc1f016015d08acd0079','8aaf0aab6011fc1f016015d0ecb4007a','8aaf0aab601f47bf01602054ed180053','8aaf0aab601f47bf01602051c0ad0051','8aaf0aab601f47bf01602052c95d0052')";
		String sql="select  oc_id   from t_personal_user_coupon where user_id='"+userid+"' and oc_id in (select coupon_id from t_operator_1212_coupons)";
		Query query = em.createNativeQuery(sql);
		List<String> rows = query.getResultList();
		HashSet<String> set = new HashSet<>();
		set.addAll(rows);
		HashMap map = new HashMap();
		map.put("total", set.size());

		map.put("result", set);
		return map;
	}

	public Boolean checkisroomOrderCommodity(String cid) {

		String  sql ="select count(1)  from t_provide_commodity where   room_id is not null and id='"+cid+"'";
		Query nativeQuery = em.createNativeQuery(sql);

		List<Integer> resultList = nativeQuery.getResultList();
		System.out.println(resultList.get(0));
		if ((resultList.get(0)+"").equals("0")) {
			return  false;
		}
		return true;

	}

	public Boolean checkisxingzcaigouOrderCommodity(String sid) {

		String sql="select count(1) from t_operator_server_class where server_name='行政采购'  and id='"+sid+"'";
		Query nativeQuery = em.createNativeQuery(sql);

		List<Integer> resultList = nativeQuery.getResultList();
		System.out.println(resultList.get(0));
		if ((resultList.get(0)+"").equals("0")) {
			return  false;
		}
		return true;
	}

	public Map findUsefulCouponsByPageforxz(String sid, String uid, Double realPrice, Integer pageNumber, Integer pageSize, Integer orderBy) {
		List<String> lCouponsLimitList = em.createNativeQuery("select valuess from t_key_value where keyss='lapse_coupons_amount_limit'").getResultList();
		double lCouponsLimit = Double.parseDouble(lCouponsLimitList.get(0) + "");
		List<String> ratioList = em.createNativeQuery("select valuess from t_key_value where keyss='direct_reduction_ratio'").getResultList();
		double ratio = Double.parseDouble(ratioList.get(0) + "") / 100;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0'  and puc.expiredDate>='"+time+"' and c.serverid='"+sid+"' and u.id='"+uid+"' and ((c.couponstype='1' and c.fullamount<="+realPrice+") or (c.couponstype!='1' and c.lapseamount<="+realPrice*ratio+" and "+realPrice+">="+lCouponsLimit+"))";
		String jpqlOrderBy = null;
		if (orderBy == null || orderBy == 1) {
			jpqlOrderBy = jpql+ " order by c.couponstype ,puc.startDate desc";
		}
		if (orderBy!=null && orderBy == 2) {
			jpqlOrderBy =jpql + " order by c.couponstype ,puc.expiredDate";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
/*		Iterator<Coupons> iterator = list.iterator();
		while (iterator.hasNext()) {
			if (realPrice >= iterator.next().getFullamount()) {
				//do nothing
			} else {
				iterator.remove();
			}
		}*/
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}

	public Map findNoUsefulCouponsByPageforxz(String sid, String uid, Double realPrice, Integer pageNumber, Integer pageSize, Integer orderBy) {
		List<String> lCouponsLimitList = em.createNativeQuery("select valuess from t_key_value where keyss='lapse_coupons_amount_limit'").getResultList();
		double lCouponsLimit = Double.parseDouble(lCouponsLimitList.get(0) + "");
		List<String> ratioList = em.createNativeQuery("select valuess from t_key_value where keyss='direct_reduction_ratio'").getResultList();
		double ratio = Double.parseDouble(ratioList.get(0) + "") / 100;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0'  and puc.expiredDate>='"+time+"'  and u.id='"+uid+"' and ((c.couponstype='1' and c.fullamount>"+realPrice+") or (c.couponstype!='1' and (c.lapseamount>"+realPrice*ratio+" or "+realPrice+"<"+lCouponsLimit+")) or c.serverid!='"+sid+"')";
		String jpqlOrderBy = null;
		if (orderBy == null || orderBy == 1) {
			jpqlOrderBy = jpql+ " order by c.couponstype ,puc.startDate desc";
		}
		if (orderBy!=null && orderBy == 2) {
			jpqlOrderBy =jpql + " order by c.couponstype ,puc.expiredDate";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
/*		Iterator<Coupons> iterator = list.iterator();
		while (iterator.hasNext()) {
			if (realPrice >= iterator.next().getFullamount()) {
				//do nothing
			} else {
				iterator.remove();
			}
		}*/
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}
	/**
	 *
	 * @Description: 查询用户各个状态的订单数量 
	 * @param userid 用户id
	 * @param @return
	 * @author Yvonne_MGL
	 * @date 2018年1月5日 下午2:39:35    
	 * @return Map   
	 * @throws 
	 */
	public Map getOrderStateNum(String userid){
		Map map=new HashMap();
		//待支付1
		String jpqltoBePaid=     "select o from ProvideOrderForm o join o.user u where o.orderStatus='1' and u.id='"+userid+"'";
		//服务中3
		String jpqlinServer=     "select o from ProvideOrderForm o join o.user u where o.orderStatus='3' and u.id='"+userid+"'";
		//待确认4
		String jpqltoBeConfirmed="select o from ProvideOrderForm o join o.user u where o.orderStatus='4' and u.id='"+userid+"'";
		//待评价6
		String jpqltoBeEvaluated="select o from ProvideOrderForm o join o.user u where o.orderStatus='6' and u.id='"+userid+"'";
		//退款中5 9 10 11
		String jpqlrefunding=    "select o from ProvideOrderForm o join o.user u where o.orderStatus in ('5','9','10','11') and u.id='"+userid+"'";

		Query querytoBePaid = em.createQuery(jpqltoBePaid);
		List<ProvideOrderForm> listtoBePaid=querytoBePaid.getResultList();
		map.put("toBePaid", listtoBePaid.size());

		Query queryinServer = em.createQuery(jpqlinServer);
		List<ProvideOrderForm> listinServer=queryinServer.getResultList();
		map.put("inServer", listinServer.size());

		Query querytoBeConfirmed = em.createQuery(jpqltoBeConfirmed);
		List<ProvideOrderForm> listtoBeConfirmed=querytoBeConfirmed.getResultList();
		map.put("toBeConfirmed", listtoBeConfirmed.size());

		Query querytoBeEvaluated = em.createQuery(jpqltoBeEvaluated);
		List<ProvideOrderForm> listtoBeEvaluated=querytoBeEvaluated.getResultList();
		map.put("toBeEvaluated", listtoBeEvaluated.size());

		Query queryrefunding = em.createQuery(jpqlrefunding);
		List<ProvideOrderForm> listrefunding=queryrefunding.getResultList();
		map.put("refunding", listrefunding.size());

		return map;
	}
	/* *
	 * @Description: 根据活动id分页查询可用的优惠卷的dao层
	 * @author JTing
	 * @date 2018/03/15 13:34
	 * @param [userId, pageNumber, pageSize, orderBy, payMoney]
	 * @return java.util.Map
	 */
	public  Map findUsefulCouponsForActiveByPage(String userId, Integer pageNumber, Integer pageSize, Integer orderBy, Double payMoney) {
		List<String> lCouponsLimitList = em.createNativeQuery("select valuess from t_key_value where keyss='lapse_coupons_amount_limit'").getResultList();
		double lCouponsLimit = Double.parseDouble(lCouponsLimitList.get(0) + "");
		List<String> ratioList = em.createNativeQuery("select valuess from t_key_value where keyss='direct_reduction_ratio'").getResultList();
		double ratio = Double.parseDouble(ratioList.get(0) + "") / 100;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0'  and puc.expiredDate>='"+time+"' and c.serverid ='0' and u.id='"+userId+"' and ((c.couponstype='1'  and c.fullamount<="+payMoney +") or (c.couponstype!='1' and c.lapseamount<="+payMoney*ratio+" and "+payMoney+">="+lCouponsLimit+"))";
		String jpqlOrderBy = null;
		if (orderBy == null || orderBy == 1) {
			jpqlOrderBy = jpql+ " order by c.couponstype ,puc.startDate desc";
		}
		if (orderBy!=null && orderBy == 2) {
			jpqlOrderBy =jpql + " order by c.couponstype ,puc.expiredDate";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}
	/* *
	 * @Description:根据活动id分页查询不可用的优惠卷的dao层
	 * @author JTing
	 * @date 2018/03/15 13:35
	 * @param [userId, pageNumber, pageSize, orderBy, payMoney]
	 * @return java.util.Map
	 */
	public Map findNoUsefulCouponsForActiveByPage(String userId, Integer pageNumber, Integer pageSize, Integer orderBy, Double payMoney) {
		List<String> lCouponsLimitList = em.createNativeQuery("select valuess from t_key_value where keyss='lapse_coupons_amount_limit'").getResultList();
		double lCouponsLimit = Double.parseDouble(lCouponsLimitList.get(0) + "");
		List<String> ratioList = em.createNativeQuery("select valuess from t_key_value where keyss='direct_reduction_ratio'").getResultList();
		double ratio = Double.parseDouble(ratioList.get(0) + "") / 100;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String time=format.format(date);
		String jpql="select new guoanmaker.personal.business.modelnobind.Coupons"
				+ "(puc.id,puc.isUsed,c.couponsname,puc.startDate,puc.expiredDate,c.couponstype,"
				+ "c.lapseamount,c.fullamount,c.subamount,c.discount,c.serverid,c.servername) from PersonalUserCoupon puc left join puc.user u left join puc.operatorCoupon c "
				+ "where puc.isUsed='0'  and puc.expiredDate>='"+time+"'  and u.id='"+userId+"' and (c.serverid !='0' or (c.couponstype='1'  and c.fullamount>"+payMoney +")  or (c.couponstype!='1'  and (c.lapseamount>"+payMoney*ratio +" or "+payMoney+" <"+lCouponsLimit+" )))";
		String jpqlOrderBy = null;
		if (orderBy == null || orderBy == 1) {
			jpqlOrderBy = jpql+ " order by c.couponstype ,puc.startDate desc";
		}
		if (orderBy!=null && orderBy == 2) {
			jpqlOrderBy =jpql + " order by c.couponstype ,puc.expiredDate";
		}
		if(pageNumber==null){
			pageNumber=0;
		}
		if (pageSize==null) {
			pageSize=10;
		}
		Query query = em.createQuery(jpqlOrderBy);
		List<Coupons> list = query.getResultList();
		Map map=new HashMap();
		map.put("totle", list.size());
		query.setFirstResult(pageNumber*pageSize); // 序号从0 开始
		query.setMaxResults(pageSize);

		List<Coupons> result = query.getResultList();
		map.put("result", result);

		return map;
	}
}
